const mysql = require('mysql'); // 引入操作 mysql 的包
const express = require('express') // 引入 express 框架
const router = express.Router() // 注册路由
const parseJson = require('../util/parsejson') // 解析json
const dateFormat = require('../util/parsedate'); // 获取当前时间
const {
    v4: uuidv4
} = require('uuid');
const http = require('http')
const cheerio = require('cheerio') // 分析网站源代码的包
const HOST = 'http://www.gov.cn' // 封装域名

// 创建链接
let connection = mysql.createConnection({
    host: 'localhost', // 链接名称
    user: 'root', // 用户名
    password: 'root', // 密码
    database: 'todomvc' // 数据库名称
});
// 建立连接
connection.connect();

// 执行 SQL 语句的方法
function query(sql) {
    return new Promise((resolve, reject) => {
        connection.query(sql, (error, results, fields) => {
            if (error) return reject(error);
            resolve(results, fields)
        });
    })
}

// 查询所有数据方法
router.get('/getAllTodo', async (req, res, next) => {
    try {
        // 获取get请求过来的参数
        let urldata = req.query
        // 添加数据到数据库
        let searchsql =
            `SELECT * FROM todolist WHERE status=${urldata.status} ORDER BY createTime DESC`
        let todolist = await query(searchsql)
        res.status(200).json({
            code: 0000,
            message: "查询成功",
            result: todolist
        })
    } catch (error) {
        next(error)
    }
})


// 增加待办方法
router.post('/addTodo', async (req, res, next) => {
    try {
        let data = req.body
        data.id = uuidv4() // 自动生成 uuid
        data.createTime = dateFormat(new Date) // 自动添加创建时间
        // 添加数据到数据库
        let addSQL = `INSERT INTO todolist VALUES(${parseJson(data)})`
        // 执行sql语句
        await query(addSQL)
        // 返回执行结果
        res.status(200).json({
            code: 0000,
            message: "添加成功"
        })
    } catch (error) {
        next(error)
    }
})

// 更新todo内容方法
router.post('/updateTodo', async (req, res, next) => {
    try {
        let data = req.body;
        let sql = `UPDATE todolist SET content="${data.content}" WHERE id="${data.id}"`
        await query(sql)
        res.status(200).json({
            code: 0000,
            message: '修改成功'
        })
    } catch (error) {
        next(error)
    }
})

// 更新待办状态方法
router.post('/updateStatus', async (req, res, next) => {
    try {
        let data = req.body;
        let sql = `UPDATE todolist SET status="${data.status}" WHERE id="${data.id}"`
        await query(sql)
        res.status(200).json({
            code: 0000,
            message: '修改成功'
        })
    } catch (error) {
        next(error)
    }
})

// 删除待办方法
router.post('/deleteTodo', async (req, res, next) => {
    try {
        let data = req.body;
        let sql = `DELETE FROM todolist WHERE id='${data.id}'`
        await query(sql)
        res.status(200).json({
            code: 0000,
            message: '删除成功'
        })
    } catch (error) {
        next(error)
    }
})

// 查询各个状态的待办总数
router.get('/getStatusCount', async (req, res, next) => {
    try {
        // 待完成数量
        let waitForCount = await
        query('SELECT COUNT(*) as COUNT FROM todolist WHERE status=1')
        // 已完成数量
        let completedCount = await
        query('SELECT COUNT(*) as COUNT FROM todolist WHERE status=2')
        // 回收站数量
        let recycleBinCount = await
        query('SELECT COUNT(*) as COUNT FROM todolist WHERE status=3')
        res.status(200).json({
            code: 0000,
            message: "查询成功",
            result: {
                waitForCount: waitForCount[0].COUNT,
                completedCount: completedCount[0].COUNT,
                recycleBinCount: recycleBinCount[0].COUNT,
            }
        })

    } catch (error) {
        next(error)
    }
})

// 爬取中国政府网获取最新要闻
router.get('/newzhengce', (req, res) => {
    // 创建http请求，获取网站源代码
    let reqest = http.request(HOST + '/xinwen/yaowen.htm', html => {
        // 存放页面数据
        let chunks = []
        html.on('data', c => chunks.push(c))
        html.on('end', () => {
            let htmlStr = Buffer.concat(chunks).toString('utf-8')
            let $ = cheerio.load(htmlStr)
            let nodeList = $('.news_box .list_1 ul li h4')
            let datalist = Array.prototype.map.call(nodeList, node => {
                return {
                    title: $(node).children('a').text(),
                    url: encodeURI(HOST + $(node).children('a').attr('href')),
                    date: $(node).children('span').text(),
                }
            })
            res.status(200).json(datalist)
        })
    })
    // 发送请求
    reqest.end()
})

// 导出路由
module.exports = router;